#!/usr/bin/env python3
# coding=utf-8
# @Time    : 2017/6/3 11:34
# @Author  : Shawn
# @Blog    : https://blog.just666.cn
# @Email   : shawnbluce@gmail.com
# @purpose : 演示Python_API

from flask import Flask, request
import json
import pymysql
import time

app = Flask(__name__)
database = pymysql.connect("115.29.52.14", "shawn", "zhangHAO8", "student")
cursor = database.cursor()


def build_json(search_list=None, e_msg=None) -> str:
    json_data = {'time': time.time(), 'search_list': search_list,
                 'e_msg': e_msg}
    return json.dumps(json_data)


@app.route('/', methods=['GET'])
def index():
    return '<h1>hello,world</h1>'


@app.route('/v1/student', methods=['POST'])  # 路径为/v1/student，方法为POST
def add_student():
    data = request.get_data().decode('utf-8')   # 将客户端传来的数据解码
    json_data = json.loads(data)    # 将数据转为Json

    # 从Json中获取数据
    name = json_data['name']
    number = str(json_data['number'])
    python = json_data['python']
    cpp = json_data['cpp']
    os = json_data['os']
    network = json_data['network']

    # 计算总分平均分
    total = python + cpp + os + network
    ave = total / 4

    # 查询数据库中是否有该学生的信息
    sql = "SELECT COUNT(*) FROM student.marks WHERE number=\"%s\"" % number
    cursor.execute(sql)
    count = cursor.fetchall()[0][0]
    database.commit()
    if count >= 1:  # 该学生信息已经存在，返回400错误
        return build_json(e_msg="student early exist"), 400

    # 向数据库中插入数据
    sql = "INSERT INTO student.marks (name, number, python, os, network, cpp, total, ave)" \
          "VALUES (\"%s\", \"%s\", %s, %s, %s, %s, %s, %s)" % (name, number, python, os,
                                                               network, cpp, total, ave)
    cursor.execute(sql)
    database.commit()

    # 请求成功，返回201状态码
    return build_json(), 201


# 路径为/v1/student, 方法为DELETE
@app.route('/v1/student/number=<number>', methods=['DELETE'])
def delete_student(number):
    if not number.isdigit():    # 判断number是否合法
        return build_json(e_msg="number should be digit"), 403
    sql = "DELETE FROM student.marks WHERE number=\"%s\"" % number
    cursor.execute(sql)
    database.commit()
    return build_json(), 204


@app.route('/v1/student/sort_by=<sort_by>', methods=['GET'])
def show_student(sort_by):
    # 判断排序的key是否正确
    if sort_by not in ['python', 'cpp', 'os', 'network', 'total', 'ave']:
        return build_json(e_msg="sort_by key not found"), 404

    # 构建查询SQL
    sql = "SELECT name, number, python, cpp, os, network, total, ave FROM student.marks ORDER BY %s DESC" % sort_by
    cursor.execute(sql)
    database.commit()
    res = cursor.fetchall()  # 获取查询结果

    # 构建查询结果Json
    res_list = {}
    count = 0
    for i in res:
        res_list['item' + str(count)] = {
            'name': i[0],
            'number': i[1],
            'python': i[2],
            'cpp': i[3],
            'os': i[4],
            'network': i[5],
            'total': i[6],
            'ave': [i[7]]
        }
        count += 1
    return build_json(search_list=res_list), 200


@app.route('/v1/student', methods=['PUT'])
def modify_student():
    data = request.get_data().decode('utf-8')
    json_data = json.loads(data)

    # 从Json中获取数据
    name = json_data['name']
    number = str(json_data['number'])
    python = json_data['python']
    cpp = json_data['cpp']
    os = json_data['os']
    network = json_data['network']

    # 查询数据库中是否有该学生的信息
    sql = "SELECT COUNT(*) FROM student.marks WHERE number=\"%s\"" % number
    cursor.execute(sql)
    count = cursor.fetchall()[0][0]
    database.commit()
    if count < 1:  # 该学生信息不存在，返回404错误
        return build_json(e_msg="student not found"), 404

    # 计算总分平均分
    total = python + cpp + os + network
    ave = total / 4

    # 向数据库中插入数据
    sql = "UPDATE student.marks SET name=\"%s\", python=%s, cpp=%s, os=%s, network=%s, total=%s, ave=%s WHERE number=\"%s\"" \
          % (name, python, cpp, os, network, total, ave, number)
    cursor.execute(sql)
    database.commit()

    # 请求成功，返回201状态码
    return build_json(), 201


with app.test_request_context():
    app.run()
